﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace kim.Common
{
    public class SqlDbHelper
    {
        #region 公用方法
        public static SqlConnection GetCon()
        {
            //连接web.conf 字符串
            string connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
            SqlConnection sqlCon = null;
            try
            {
                sqlCon = new SqlConnection(connectionString);
                sqlCon.Open();
            }
            catch (Exception e)
            {
                throw e;
            }
            return sqlCon;
        }
        //关闭数据库连接
        public static void CloseCon(SqlConnection sqlCon)
        {
            //判断突然中断 关闭连接
            if (sqlCon.State == ConnectionState.Broken)
            {
                sqlCon.Close();
            }
            else
            {
                sqlCon.Close();
            }

        }
        public static int GetPageCurrent(string tbname,string where)
        { string strSql=string.Empty;
        if (tbname != "")
            strSql = "select count(*) from " + tbname;
        if (where != "")
            strSql += " where " + where;
           return Convert.ToInt32(GetScalar(strSql, null));
        }
        #endregion

        #region 简查询及带参数查询操作

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetScalar(string SQLString, params SqlParameter[] values)
        {
            using (SqlConnection connection = GetCon())
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, values);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                    finally
                    {
                        CloseCon(connection);
                    }
                }
            }
        }
                   
 

        /// <summary>
        /// 无参数的返回DataSet
        /// </summary>
        public static DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = null;
            try
            {
                sqlcon = GetCon();
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);


                //声明适配器
                SqlDataAdapter sqlDA = new SqlDataAdapter(sqlcom);

                //填充到适配器中
                sqlDA.Fill(ds);

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            finally
            {
                CloseCon(sqlcon);
            }
            return ds;
        }
        /// <summary>
        /// 带参数的返回DataSet
        /// </summary>
        public static DataSet GetDataSet(string sql, SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = null;
            try
            {
                using (sqlcon = GetCon())
                {
                    SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
                    //判断是否有参数
                    PrepareCommand(sqlcom, sqlcon, null, sql, values);
                    //声明适配器
                    SqlDataAdapter sqlDA = new SqlDataAdapter(sqlcom);

                    //填充到 适配器中
                    sqlDA.Fill(ds);
                }
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            finally
            {
                CloseCon(sqlcon);
            }
            return ds;
        }
        /// <summary>
        /// 返回DataTable
        /// </summary>
        public static DataTable GetDataTable(string sql, SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlConnection sqlcon = null;
            try
            {
                sqlcon = GetCon();
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
                PrepareCommand(sqlcom, sqlcon, null, sql, values);
                //声明适配器
                SqlDataAdapter sqlDA = new SqlDataAdapter(sqlcom);

                //填充到 适配器中
                sqlDA.Fill(ds);

            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            finally
            {
                CloseCon(sqlcon);
            }
            return ds.Tables["0"];
        }
        /// <summary>
        /// 无参数数据查询
        /// </summary>
        public static int ExecuteCommand(string sql)
        {
            SqlConnection sqlcon = GetCon();
            try
            {
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);


                int num = sqlcom.ExecuteNonQuery();
                return num;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            finally
            {
                CloseCon(sqlcon);
            }
        }
        /// <summary>
        /// (带参数)执行数据语句
        /// </summary>
        public static int ExecuteCommand(string sql, SqlParameter[] values)
        {
            SqlConnection sqlcon = GetCon();
            try
            {
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
                PrepareCommand(sqlcom, sqlcon, null, sql, values);
                int num = sqlcom.ExecuteNonQuery();
                return num;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            finally
            {
                CloseCon(sqlcon);
            }
        }
        /// <summary>
        /// 返回DataReader
        /// </summary>
        public static SqlDataReader GetReader(string sql, SqlParameter[] values)
        {
            SqlConnection sqlcon = GetCon();
            SqlDataReader sqldr = null;
            try
            {
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
                PrepareCommand(sqlcom, sqlcon, null, sql, values);
                sqldr = sqlcom.ExecuteReader();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            return sqldr;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        #endregion
        #region 存储过程操作

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader GetDataReaderProc(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = GetCon())
            {
                SqlDataReader returnReader;
                SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;
                returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return returnReader;
            }

        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetProc(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = GetCon())
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, "t1");
                connection.Close();
                return dataSet;
            }
        }
        public static DataSet GetDataSetProc(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            using (SqlConnection connection = GetCon())
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.Output) &&(parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }


        #endregion
    }
}
